package cn.source.system.service.impl;

import java.io.BufferedReader;
import java.io.File;
import java.io.FileReader;
import java.sql.SQLException;
import java.util.Date;
import java.util.List;
import java.util.Map;
import java.util.concurrent.TimeUnit;
import java.util.stream.Collectors;

import cn.hutool.extra.spring.SpringUtil;
import cn.source.common.utils.StringUtils;
import cn.source.system.domain.*;
import cn.source.system.service.IDbRecoveryService;
import cn.source.system.service.IDbTaskService;
import cn.source.system.utils.ConnUtils;
import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.fastjson2.JSON;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.beans.factory.support.DefaultListableBeanFactory;
import org.springframework.data.redis.core.ListOperations;
import org.springframework.data.redis.core.RedisTemplate;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.scheduling.annotation.Async;
import org.springframework.stereotype.Service;
import cn.source.system.mapper.DbConnMapper;
import cn.source.system.service.IDbConnService;

/**
 * 数据库连接Service业务层处理
 *
 * @author sourcebyte.vip
 * @date 2025-02-21
 */
@Service
@Slf4j
public class DbConnServiceImpl implements IDbConnService {
    @Autowired
    private DbConnMapper dbConnMapper;
    @Autowired
    private IDbRecoveryService dbRecoveryService;

    @Autowired
    private RedisTemplate<Object, Object> redisTemplate;

    @Autowired
    private IDbTaskService dbTaskService;

    @Value("${backup.logMaxLength}")
    private Integer logMaxLength;

    @Value("${backup.csv.fieldsTerminatedBy}")
    private String fieldsTerminatedBy;
    @Value("${backup.csv.linesTerminatedBy}")
    private String linesTerminatedBy;
    @Value("${backup.csv.enclosedBy}")
    private String enclosedBy;

    /**
     * 查询数据库连接
     *
     * @param id 数据库连接主键
     * @return 数据库连接
     */
    @Override
    public DbConn selectDbConnById(Long id) {
        return dbConnMapper.selectDbConnById(id);
    }

    /**
     * 查询数据库连接列表
     *
     * @param dbConn 数据库连接
     * @return 数据库连接
     */
    @Override
    public List<DbConn> selectDbConnList(DbConn dbConn) {
        return dbConnMapper.selectDbConnList(dbConn);
    }

    /**
     * 新增数据库连接
     *
     * @param dbConn 数据库连接
     * @return 结果
     */
    @Override
    public int insertDbConn(DbConn dbConn) {
        testconn(dbConn);
        return dbConnMapper.insertDbConn(dbConn);
    }

    /**
     * 修改数据库连接
     *
     * @param dbConn 数据库连接
     * @return 结果
     */
    @Override
    public int updateDbConn(DbConn dbConn) {
        testconn(dbConn);
        return dbConnMapper.updateDbConn(dbConn);
    }

    /**
     * 批量删除数据库连接
     *
     * @param ids 需要删除的数据库连接主键
     * @return 结果
     */
    @Override
    public int deleteDbConnByIds(Long[] ids) {
        return dbConnMapper.deleteDbConnByIds(ids);
    }

    /**
     * 删除数据库连接信息
     *
     * @param id 数据库连接主键
     * @return 结果
     */
    @Override
    public int deleteDbConnById(Long id) {
        return dbConnMapper.deleteDbConnById(id);
    }

    @Override
    public int testconn(DbConn dbConn) {
        //创建Druid连接池
        DruidDataSource druidDataSource = new DruidDataSource();
        druidDataSource.setUrl(dbConn.getConnUrl());
        druidDataSource.setUsername(dbConn.getDbUser());
        druidDataSource.setPassword(dbConn.getDbPass());
        try {
            druidDataSource.getConnection(2000);
            //将数据源加入到Spring环境中
            DefaultListableBeanFactory beanFactory = (DefaultListableBeanFactory) SpringUtil.getBeanFactory();
            JdbcTemplate jdbcTemplate = new JdbcTemplate(druidDataSource);
            if (beanFactory.containsBean(dbConn.getConnName())) {
                JdbcTemplate oldTmp = beanFactory.getBean(dbConn.getConnName(), JdbcTemplate.class);
                DruidDataSource oldDs = (DruidDataSource) oldTmp.getDataSource();
                oldDs.close();
                beanFactory.destroySingleton(dbConn.getConnName());//销毁bean
            }
            //重新注册
            beanFactory.registerSingleton(dbConn.getConnName(), jdbcTemplate);

        } catch (SQLException e) {
            druidDataSource.close();//出现异常，关闭连接池
            //获取异常原因
            String message = e.getCause().getMessage();
            log.error("数据库连接异常{}", message);
            throw new RuntimeException(message);
        }
        return 1;
    }

    @Override
    public List<Map<String, Object>> getTableList(String connName, String tableName) {
        String param = "";
        if (!StringUtils.isEmpty(tableName)) {
            param = " and TABLE_NAME like '%" + tableName + "%'";
        }
        DbConn dbConn = findOneByConnName(connName);
        ConnInfo info = ConnUtils.urlParse(dbConn.getConnUrl());
        String dbName = info.getDbName();
        //拼接sql
        String sql = "SELECT TABLE_NAME, TABLE_TYPE FROM information_schema.TABLES WHERE TABLE_SCHEMA = '" + dbName + "' AND (TABLE_TYPE = 'BASE TABLE' OR TABLE_TYPE = 'VIEW')" + param + " order by TABLE_TYPE";
        List<Map<String, Object>> showTables = ConnUtils.getConnQueryResult(connName, sql);
        return showTables;
    }

    @Override
    public DbConn findOneByConnName(String connName) {
        return dbConnMapper.findOneByConnName(connName);
    }

    /**
     * 恢复任务
     *
     * @param recoveryDTO
     */
    @Override
    @Async
    public void recovery(RecoveryDTO recoveryDTO) {

        StringBuffer logBuffer = new StringBuffer();//日志缓存
        StringBuffer errBuffer = new StringBuffer();//错误信息缓存

        Integer recoveryId = recoveryDTO.getRecoveryId();
        DbRecovery dbRecovery = dbRecoveryService.selectDbRecoveryById(Long.valueOf(recoveryId));
        dbRecovery.setDbTaskId(recoveryDTO.getDbTaskId());//设置任务id
        long taskStart = System.currentTimeMillis();

        List<RecoveryTable> recoveryTables = recoveryDTO.getRecoveryTables();

        //判断文件是否已经被删除
        String filePath = recoveryTables.get(0).getFileAbsolutePath();
        File file = new File(filePath);
        File parentFile = file.getParentFile();
        if (!parentFile.exists()) {
            String log = "文件已经被删除，无法恢复";
            insertLog(dbRecovery, log, true, logBuffer, errBuffer, false);
            throw new RuntimeException(log);
        }
        //判断格式
        RecoveryTable recoveryTable = recoveryTables.get(0);
        String fileName = recoveryTable.getFileName();
        int totalCnt = 0;
        if (fileName.endsWith(".sql")) {
            totalCnt = recoverySql(dbRecovery, recoveryTables, logBuffer, errBuffer);
        } else {
            totalCnt = recoveryCsv(dbRecovery, recoveryTables, logBuffer, errBuffer);
        }
        long end = System.currentTimeMillis();
        String log = "导入完成 影响总行数：" + totalCnt + "\t花费时间" + (end - taskStart) / 1000 + "s";
        insertLog(dbRecovery, log, true, logBuffer, errBuffer, false);
    }

    /**
     * @param connName     连接的名称
     * @param tablesToFile 备份的表信息
     * @param task         备份任务的信息
     */
    public void createRecoveryTask(String connName, String tablesToFile, DbTask task) {
        List<RecoveryTable> tableToFiles = JSON.parseArray(tablesToFile, RecoveryTable.class);
        List<String> tables = tableToFiles.stream().map(RecoveryTable::getTableName).collect(Collectors.toList());

        DbConn oneByConnName = findOneByConnName(connName);
        String connUrl = oneByConnName.getConnUrl();
        ConnInfo info = ConnUtils.urlParse(connUrl);
        String dbName = info.getDbName();

        DbRecovery dbRecovery = new DbRecovery();
        dbRecovery.setTables(String.join("#", tables));
        dbRecovery.setConnName(connName);
        dbRecovery.setDbName(dbName);
        dbRecovery.setStartTime(new Date());
        int i1 = dbRecoveryService.insertDbRecovery(dbRecovery);
        Long id = dbRecovery.getId();

        for (RecoveryTable tableToFile : tableToFiles) {
            tableToFile.setRecoveryConnName(connName);
        }

        //这里作为任务的一部分，直接使用调度任务的线程，而不是新开线程
        RecoveryDTO dto = new RecoveryDTO();
        dto.setRecoveryId(id.intValue());
        dto.setRecoveryTables(tableToFiles);
        dto.setDbTaskId(task.getId().intValue());
        recovery(dto);
    }


    private int recoveryCsv(DbRecovery dbRecovery, List<RecoveryTable> recoveryTables, StringBuffer logBuffer, StringBuffer errBuffer) {
        int totalCnt = 0;
        for (RecoveryTable recoveryTable : recoveryTables) {
            try {
                //重新加载数据需要truncate一下
                String tranSql = "truncate table " + recoveryTable.getTableName();
                long start = System.currentTimeMillis();
                insertLog(dbRecovery, tranSql, false, logBuffer, errBuffer, false);
                int cnt = ConnUtils.updateOutFile(recoveryTable.getRecoveryConnName(), tranSql);
                long end = System.currentTimeMillis();
                String log = "影响行数：" + cnt + "\t花费时间" + (end - start) + "ms";
                insertLog(dbRecovery, log, false, logBuffer, errBuffer, false);

                String fileAbsolutePath = recoveryTable.getFileAbsolutePath();
                String path = fileAbsolutePath.replace("\\", "\\\\");

                String sql = "LOAD DATA LOCAL INFILE " + "'" + path + "'" +
                        " INTO TABLE " + recoveryTable.getTableName() + " FIELDS TERMINATED BY '" + fieldsTerminatedBy + "' ENCLOSED BY '" + enclosedBy + "' LINES TERMINATED BY '" + linesTerminatedBy + "'";
                start = System.currentTimeMillis();
                insertLog(dbRecovery, sql, false, logBuffer, errBuffer, false);
                cnt = ConnUtils.updateOutFile(recoveryTable.getRecoveryConnName(), sql);
                end = System.currentTimeMillis();
                log = "影响行数：" + cnt + "\t花费时间" + (end - start) + "ms";
                insertLog(dbRecovery, log, false, logBuffer, errBuffer, false);
                testException(true);
            } catch (Exception e) {
                procException(dbRecovery, e, logBuffer, errBuffer);
            }
        }
        return totalCnt;
    }

    //todo 测试异常
    static int i = 0;

    public static void testException(boolean b) {
//        i++;
//        if (i % 30 == 0 || b) {
//            throw new RuntimeException("测试异常");
//        }
    }

    private int recoverySql(DbRecovery dbRecovery, List<RecoveryTable> recoveryTables, StringBuffer logBuffer, StringBuffer errBuffer) {
        int totalCnt = 0;
        for (RecoveryTable recoveryTable : recoveryTables) {
            try (BufferedReader reader = new BufferedReader(new FileReader(recoveryTable.getFileAbsolutePath()))) {
                String line;
                StringBuilder sqlStatement = new StringBuilder();
                while ((line = reader.readLine()) != null) {
                    line = line.trim();
                    if (line.isEmpty() || line.startsWith("--")) {
                        continue;
                    }
                    sqlStatement.append(line).append(" ");//添加空格，避免sql错误
                    if (line.endsWith(";")) {
                        //插入日志
                        insertLog(dbRecovery, sqlStatement.toString(), false, logBuffer, errBuffer, false);
                        long start = System.currentTimeMillis();
                        int cnt = 0;
                        try {
                            cnt = ConnUtils.updateOutFile(recoveryTable.getRecoveryConnName(), sqlStatement.toString());
                            totalCnt += cnt;
                            testException(false);
                        } catch (Exception e) {
                            procException(dbRecovery, e, logBuffer, errBuffer);
                        } finally {
                            sqlStatement.setLength(0);
                        }
                        long end = System.currentTimeMillis();
                        String log = "影响行数：" + cnt + "\t花费时间" + (end - start) + "ms";
                        insertLog(dbRecovery, log, false, logBuffer, errBuffer, false);
                    }
                }
            } catch (Exception e) {
                String log = e.getCause() != null ? e.getCause().getMessage() : e.getMessage();
                insertLog(dbRecovery, log, true, logBuffer, errBuffer, true);
            }
        }
        return totalCnt;
    }

    private void procException(DbRecovery dbRecovery, Exception e, StringBuffer logBuffer, StringBuffer errBuffer) {
        e.printStackTrace();
        //发生错误，也插入
        if (e.getCause() instanceof SQLException) {
            SQLException sqlException = (SQLException) e.getCause();
            int errorCode = sqlException.getErrorCode();
            String message = sqlException.getMessage();
            String log = "错误码：" + errorCode + "\t错误信息：" + message + "\n";
            insertLog(dbRecovery, log, false, logBuffer, errBuffer, true);
        } else {
            String log = e.getCause() != null ? e.getCause().getMessage() : e.getMessage();
            insertLog(dbRecovery, log, false, logBuffer, errBuffer, true);
        }
    }


    @Override
    public String recoveryLastRowLog(Integer recoveryId) {
        //查询最后一条数据
        String key = "db_recovery_log_" + recoveryId;
        ListOperations<Object, Object> listOps = redisTemplate.opsForList();
        Object o = listOps.leftPop(key, 1, TimeUnit.SECONDS);
        return o == null ? "" : o.toString();
    }

    public void insertLog(DbRecovery recovery, String logs, boolean end, StringBuffer logBuffer, StringBuffer errBuffer, boolean b) {
        String key = "db_recovery_log_" +
                recovery.getId();
        //日志太大了。为了解决这个问题，日志只取100个字符,并在最后加上...
        if (logs.length() > logMaxLength) {
            logs = logs.substring(0, logMaxLength) + "...";
        }

        if (b) {//错误日志再重新收集一次
            errBuffer.append(logs).append("\n");
        }
        ListOperations<Object, Object> listOps = redisTemplate.opsForList();
        listOps.rightPush(key, logs);
        //对错误日志进行总结
        if (end && errBuffer.length() > 0) {
            int len = errBuffer.toString().split("\n").length;
            String errLog = "执行错误次数：" + len + "\n";
            logBuffer.append(errLog);

            //发生错误时，查看系统是否进行了任务邮件配置
            Integer dbTaskId = recovery.getDbTaskId();
            if (dbTaskId != null) {
                dbTaskService.sendMailForTask(dbTaskId, errLog);
            }
            logBuffer.append(logs).append("\n");
            if (end) {
                recovery.setLogs(logBuffer.toString());
                recovery.setEndTime(new Date());
                recovery.setErrLogs(errBuffer.toString());
                dbRecoveryService.updateDbRecovery(recovery);
            }
        }
    }

    //zip压缩下载


}
